package com.lvtn.summary;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;

import model.process.MainProcess;
import model.process.PR;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteKQtoXSL {

	/**
	 * @param args
	 * @throws IOException
	 */
	public static void main(String[] args) throws IOException {
		// get pr
		PR pr = new MainProcess().getPR("data/plain.txt",
				"data/resultChangeForm.txt");

		// file excel
		String fileName = "Bao_Cao/Bao_cao_2En.xlsx";

		// Open and Modify File Excel
		XSSFWorkbook wb = null;
		FileInputStream ins = null;
		try {
			ins = new FileInputStream(fileName);
			wb = new XSSFWorkbook(ins);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		CreationHelper helper = wb.getCreationHelper();

		Sheet sheetResult = wb.getSheet("Main_6-11");

		CellStyle cellStype = wb.createCellStyle();
		cellStype.setWrapText(true);
		Font font = wb.createFont();
		font.setFontName("Times New Roman");
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setColor(IndexedColors.VIOLET.getIndex());
		cellStype.setFont(font);
		cellStype.setAlignment(CellStyle.VERTICAL_BOTTOM);
		cellStype.setBorderTop(CellStyle.BORDER_THIN);
		cellStype.setBorderRight(CellStyle.BORDER_THIN);
		cellStype.setBorderLeft(CellStyle.BORDER_THIN);
		cellStype.setBorderBottom(CellStyle.BORDER_THIN);

		// method get comment in file data write to xlsx
		String oneEntity = "input/oneEntityComments.txt";
		String plain = "data/plain.txt";
		String result = "data/resultChangeForm.txt";
		String listEntity = "data/entityOfComment.txt";

		FileReader fr = new FileReader(oneEntity);
		BufferedReader br = new BufferedReader(fr);
		String line = null;

		FileReader frPlain = new FileReader(plain);
		BufferedReader brPlain = new BufferedReader(frPlain);
		String linePlain = null;

		FileReader frResult = new FileReader(result);
		BufferedReader brResult = new BufferedReader(frResult);
		String lineResult = null;

		FileReader frListEntity = new FileReader(listEntity);
		BufferedReader brListEntity = new BufferedReader(frListEntity);
		String lineListEntity = null;

		int i = 2;
		int count = 0;
		while ((line = br.readLine()) != null) {
			linePlain = brPlain.readLine();
			lineResult = brResult.readLine();
			lineListEntity = brListEntity.readLine();

			Row row = sheetResult.createRow(i);
			Cell numCell = row.createCell(0);
			Cell commentCell = row.createCell(1);
			Cell cellListEnity = row.createCell(2);
			Cell humanPos = row.createCell(3);
			Cell humanNeg = row.createCell(4);
			Cell humanNeu = row.createCell(5);
			Cell sysPos = row.createCell(6);
			Cell sysNeg = row.createCell(7);
			Cell sysNeu = row.createCell(8);

			Cell pPositive = row.createCell(9);
			Cell rPositive = row.createCell(10);
			Cell pNegative = row.createCell(11);
			Cell rNegative = row.createCell(12);
			Cell pNeutral = row.createCell(13);
			Cell rNeutral = row.createCell(14);

			pPositive.setCellValue(pr.getpPositive().get(count));
			pPositive.setCellStyle(cellStype);
			rPositive.setCellValue(pr.getrPositive().get(count));
			rPositive.setCellStyle(cellStype);
			pNegative.setCellValue(pr.getpNegative().get(count));
			pNegative.setCellStyle(cellStype);
			rNegative.setCellValue(pr.getrNegative().get(count));
			rNegative.setCellStyle(cellStype);
			pNeutral.setCellValue(pr.getpNeutral().get(count));
			pNeutral.setCellStyle(cellStype);
			rNeutral.setCellValue(pr.getrNeutral().get(count));
			rNeutral.setCellStyle(cellStype);

			numCell.setCellValue(i - 1);
			numCell.setCellStyle(cellStype);

			commentCell.setCellValue(helper.createRichTextString(line));
			commentCell.setCellStyle(cellStype);

			cellListEnity.setCellValue(helper
					.createRichTextString(lineListEntity));
			cellListEnity.setCellStyle(cellStype);

			// human -> 3,4,5
			String[] strr = linePlain.split(";");
			if (strr[0].split("p:").length > 0) {
				humanPos.setCellValue(helper.createRichTextString(strr[0]
						.split("p:")[1]));
				humanPos.setCellStyle(cellStype);
			}

			if (strr[1].split("n:").length > 0) {
				humanNeg.setCellValue(helper.createRichTextString(strr[1]
						.split("n:")[1]));
				humanNeg.setCellStyle(cellStype);
			}

			if (strr[2].split("pn:").length > 0) {
				humanNeu.setCellValue(helper.createRichTextString(strr[2]
						.split("pn:")[1]));
				humanNeu.setCellStyle(cellStype);
			}

			// system -> 6,7,8

			String[] strr1 = lineResult.split(";");
			if (strr1[0].split("p:").length > 0) {
				sysPos.setCellValue(helper.createRichTextString(strr1[0]
						.split("p:")[1]));
				sysPos.setCellStyle(cellStype);
			}

			if (strr1[1].split("n:").length > 0) {
				sysNeg.setCellValue(helper.createRichTextString(strr1[1]
						.split("n:")[1]));
				sysNeg.setCellStyle(cellStype);
			}

			if (strr1[2].split("pn:").length > 0) {
				sysNeu.setCellValue(helper.createRichTextString(strr1[2]
						.split("pn:")[1]));
				sysNeu.setCellStyle(cellStype);
			}

			i++;
			count++;
		}

		// tong hop kq p,r
		String StrFormula = "AVERAGE(J3:J" + i + ")";
		String StrFormula1 = "AVERAGE(K3:K" + i + ")";
		String StrFormula2 = "AVERAGE(L3:L" + i + ")";
		String StrFormula3 = "AVERAGE(M3:M" + i + ")";
		String StrFormula4 = "AVERAGE(N3:N" + i + ")";
		String StrFormula5 = "AVERAGE(O3:O" + i + ")";

		String StrFormula6 = "AVERAGE(J" + (i + 1) + ",L" + (i + 1) + ",N"
				+ (i + 1) + ")";
		String StrFormula7 = "AVERAGE(K" + (i + 1) + ",M" + (i + 1) + ",O"
				+ (i + 1) + ")";

		Row row = sheetResult.createRow(i);

		Cell resultpPositive = row.createCell(9);
		Cell resultrPositive = row.createCell(10);
		Cell resultpNegative = row.createCell(11);
		Cell resultrNegative = row.createCell(12);
		Cell resultpNeutral = row.createCell(13);
		Cell resultrNeutral = row.createCell(14);

		resultpPositive.setCellFormula(StrFormula);
		resultpPositive.setCellStyle(cellStype);
		resultrPositive.setCellFormula(StrFormula1);
		resultrPositive.setCellStyle(cellStype);
		resultpNegative.setCellFormula(StrFormula2);
		resultpNegative.setCellStyle(cellStype);
		resultrNegative.setCellFormula(StrFormula3);
		resultrNegative.setCellStyle(cellStype);
		resultpNeutral.setCellFormula(StrFormula4);
		resultpNeutral.setCellStyle(cellStype);
		resultrNeutral.setCellFormula(StrFormula5);
		resultrNeutral.setCellStyle(cellStype);

		Row row1 = sheetResult.createRow(i + 2);
		Cell avgPText = row1.createCell(8);
		Cell avgP = row1.createCell(9);

		Row row2 = sheetResult.createRow(i + 3);
		Cell avgRText = row2.createCell(8);
		Cell avgR = row2.createCell(9);
		avgPText.setCellValue("Average P");
		avgP.setCellFormula(StrFormula6);
		avgPText.setCellStyle(cellStype);
		avgP.setCellStyle(cellStype);
		avgRText.setCellValue("Average R");
		avgRText.setCellStyle(cellStype);
		avgR.setCellFormula(StrFormula7);
		avgR.setCellStyle(cellStype);
		fr.close();
		frPlain.close();
		frResult.close();
		frListEntity.close();

		FileOutputStream fileOut = new FileOutputStream(fileName);
		wb.write(fileOut);
		fileOut.close();

	}
}
